<?php
/* +----------------------------------------------------------------------
 * 作          者：
 +----------------------------------------------------------------------
 * 创建日期：2016-12-12
 +----------------------------------------------------------------------
 * 文件描述：交叉分析
 +----------------------------------------------------------------------
 * 升级记录： 
 +----------------------------------------------------------------------
*/
class AnalysisCrossAction extends CommonAction {
	public function index() {
		//查询问卷数据
		$model = new FormModel();
		$test_data = $model->field('id, 
									title, 
									intro, 
									item_info, 
									status')
						   ->select();
		$this->assign('test_data', $test_data);
		$formid = $this->_request('formid');
		if(empty($formid)) {
			$formid = $test_data[0]['id'];
		}
		$this->assign("formid", $formid);
		
		$formdata = $model->field('id, 
								   title, 
								   intro, 
								   item_info, 
								   status')
						  ->where("id = '".$formid."'")
						  ->find();
		if(!empty($formdata['item_info'])) {
			$item_info = explode(',', $formdata['item_info']);
			foreach($item_info as $key => $value) {
				//根据模块类型获取模块内容
				$item_info_detail = explode(':', $value);
				switch ($item_info_detail[0]) {
					case 'select' :
						$data = $model->table("question_test_select")
									  ->field("'选择题' type,
									  		   code,
											   formid,
											   question,
											   score")
									  ->where("id = '".$item_info_detail[1]."'")
									  ->find();
						break;
					default :
						$data = array();
				}
				if(!empty($data)) {
					$data['item_type'] = $item_info_detail[0];
					$data['item_id'] = $item_info_detail[1];
					$item_info[$key] = $data;
				} else {
					unset($item_info[$key]);
				}
			}
			$formdata['item_list'] = $item_info;
		}
 		$this->assign('formdata', $formdata);
		$this->assign('type_list', set_field_dict_list('groups_user', 'type'));
 		
 		//查询提交内容
 		$questionid= $this->_request('questionid');
 		if(empty($questionid)) {
 			$questionid = $formdata['item_list'][0]['item_id'];
 		} 		
 		$this->assign("questionid", $questionid);
	
 		//查询得分的分布情况
 		$score = new ScoreModel();
		//组装查询语句
		$where = "b.questionid = '".$questionid."'";
		if(!empty($_REQUEST['type'])) {
			$where .= " and a.type = '".$_REQUEST['type']."'";
		}
		if(!empty($_REQUEST['school'])) {
			$where .= " and a.school = '".$_REQUEST['school']."'";
		} elseif(!empty($_REQUEST['area_code'])) {
			$where .= " and a.area_code = '".$_REQUEST['area_code']."'";
		} elseif(!empty($_REQUEST['city_code'])) {
			$where .= " and a.area_code like '".$_REQUEST['city_code']."%'";
		} elseif(!empty($_REQUEST['province_code'])) {
			$where .= " and a.area_code like '".$_REQUEST['province_code']."%'";
		}
		//获取要查询的类别
		$data_sta1 = $this->_request("data_sta1");
		$data_sta2 = $this->_request("data_sta2");
		//dump($data_sta1);
		//dump($data_sta2);
		
		switch($data_sta1) {
			case school:
				$msg1 = "学校";
				break;
			case school_type:
				$msg1 = "学校类型";
				break;
			case subject:
				$msg1 = "学科";
				break;
			case phase:
				$msg1 = "学段";
				break;
			case teach_year:
				$msg1 = "教龄";
				break;
			case birth:
				$msg1 = "年龄";
 				break;
			case grade:
				$msg1 = "年级";
				break;
			default:
				$msg1 = "性别";
		}
	switch($data_sta2) {
			case school:
				$msg2 = "学校";
				break;
			case school_type:
				$msg2 = "学校类型";
				break;
			case subject:
				$msg2 = "学科";
				break;
			case phase:
				$msg2 = "学段";
				break;
			case teach_year:
				$msg2 = "教龄";
				break;
			case birth:
				$msg2 = "年龄";
 				break;
			case grade:
				$msg2 = "年级";
				break;
			default:
				$msg2 = "性别";
		}
		$this->assign("msg1",$msg1);
		$this->assign("msg2",$msg2);
		
		//dump($msg1);
		//dump($msg2);
	
		//内连接查询每个分组中的人数
				
		if (!empty($data_sta1)	|| !empty($data_sta2)){
		if((($data_sta1)== "birth") || (($data_sta1)== "birth")){
			if(($data_sta1)== "birth"){
				$data_count = $model->query("select year(now()) - left(a.birth,4) $data_sta1, 
    											get_field_dict_name('groups_user','$data_sta2',a.$data_sta2) $data_sta2,
 												round(avg(b.score), 2) score 
 										   from groups_user a,
 										   		score b
 										  where a.id = b.groups_userid and ".$where." 
 										  group by (year(now()) - left(a.birth,4)),  $data_sta2");		
			}else{
				$data_count = $model->query("select  get_field_dict_name('groups_user','$data_sta1',a.$data_sta1) $data_sta1,
    											year(now()) - left(a.birth,4) $data_sta2,
 												round(avg(b.score), 2) score 
 										   from groups_user a,
 										   		score b
 										  where a.id = b.groups_userid and ".$where." 
 										  group by (year(now()) - left(a.birth,4)),  $data_sta2");		
				}					
			}
		else{
			$data_count = $model->query("select
											  get_field_dict_name('groups_user','$data_sta1',a.$data_sta1) $data_sta1,
											  get_field_dict_name('groups_user','$data_sta2',a.$data_sta2) $data_sta2,
											  ROUND(avg(b.score), 2) score
											from groups_user a,
											  score b
											where a.id = b.groups_userid and ".$where."
											group by a.$data_sta1, $data_sta2
											");		
			}
		}
		dump($data_count);	
		/*--if($data_sta1 == "birth") {
    		$data_count = $model->query("select year(now()) - left(a.birth,4) field_mean1, 
    											get_field_dict_name('user','gender',a.gender) field_mean2,
 												round(avg(b.score), 2) score 
 										   from groups_user a,
 										   		score b
 										  where ".$where." 
 										  group by (year(now()) - left(a.birth,4)), a.gender");										
//    		dump($data_count);
    	} elseif($data_sta == "school") {
    		$data_count = $model->query("select get_field_dict_name('groups_user','phase',a.phase) phase,
												get_field_dict_name('user','gender',a.gender) gender,
												round(avg(b.score), 2) score
										   from groups_user a,
												score b
										  where a.id = b.groups_userid and b.questionid = 'cms_def_webid_20160908173740_a8eg91roj1c'
										  group by a.phase, gender");
    	} else {
    		$data_count = $model->query("select get_field_dict_name('groups_user','phase',a.phase) phase,
												get_field_dict_name('user','gender',a.gender) gender,
												ROUND(avg(b.score), 2) score
										   from groups_user a,
												score b
										  where a.id = b.groups_userid and b.questionid = 'cms_def_webid_20160908173740_a8eg91roj1c'
										  group by a.phase, gender");
    	}
		dump($model->getLastSql());
		dump($data_count);*/
		$this->assign('data_count', $data_count);
		
		
		//横坐标组装
		$categories = array();
		$categorie = $model->query("select distinct
											  get_field_dict_name('groups_user','$data_sta1',a.$data_sta1) $data_sta1
											from groups_user a,
											  score b
											where a.id = b.groups_userid and b.questionid = '$questionid'
											group by a.$data_sta1

											");
		foreach($categorie as $key=>$value){
			$categories []= $value[$data_sta1];
			
		}
		$this->assign('categories', $categories);
		dump($categories);
		
		//横横坐标组装
		$serie = $model->query("select distinct
											  get_field_dict_name('groups_user','$data_sta2',a.$data_sta2) $data_sta2
											from groups_user a,
											  score b
											where a.id = b.groups_userid and b.questionid = '$questionid'
											group by a.$data_sta2
											");
		$name = array();
		foreach($serie as $key=>$value){
			$name[]= $value[$data_sta2];	
		}
	    $series =array(name=>'',data=>array());
		foreach($name as $keys){
			foreach($data_count as $key => $value){
				if($value[$data_sta2] == $keys){
					$series[][name]= $keys;
					$series[][data][] = $value;
				}
			}	
		}
		$this->assign('series', $series);

		
		//显示省、市、县、校结构
		$this->assign('province_list', get_province_list());	//省份列表
		//查询该省份下的市州列表
		if(!empty($_REQUEST['province_code'])) {
			$model = new CityModel();
			$city_list = $model->field("code, name")
							   ->where("province_code = '".$_REQUEST['province_code']."'")
							   ->select();
			$this->assign('city_list', $city_list);
		}
		//查询该市州下的县区列表
		if(!empty($_REQUEST['city_code'])) {
			$model = new AreaModel();
			$area_list = $model->field("code, name")
							   ->where("city_code = '".$_REQUEST['city_code']."'")
							   ->select();
			$this->assign('area_list', $area_list);
		} 
		//查询该县域下的学校列表
		if(!empty($_REQUEST['area_code'])) {
			$model = new SchoolModel();
			$school_list = $model->field("id, name")
								 ->where("area_code = '".$_REQUEST['area_code']."'")
								 ->select();
			$this->assign('school_list', $school_list);
		} else {
			$model = new SchoolModel();
			$school_list = $model->field("id, name")
								 ->select();
			$this->assign('school_list', $school_list);
		}
		$this->display();
	}
}
?>